USE [BlogIt]

DROP TABLE [BlogPostComments]
DROP TABLE [BlogPosts]
DROP TABLE [Blogs]
DROP TABLE [Profiles]
DROP TABLE [Bloggers]
DROP TABLE [webpages_OAuthMembership]
DROP TABLE [webpages_Membership]
DROP TABLE [webpages_Roles]
DROP TABLE [webpages_UsersInRoles]
DROP TABLE [Users]

CREATE TABLE [Users] (
	  [UserId] int NOT NULL IDENTITY (1,1)
	, [Username] varchar(24) NOT NULL
	, CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([UserId])
)

CREATE TABLE [Bloggers] (
	  [PK] int NOT NULL IDENTITY (1,1)
	, [UserId] int NOT NULL REFERENCES [Users]([UserId])
	, [Password] varchar(128) NULL
	, [DisplayName] varchar(32) NOT NULL
	, [Created] datetime NOT NULL DEFAULT GETDATE()
	, [Updated] datetime NOT NULL DEFAULT GETDATE()
	, [Deleted] datetime NULL
	, CONSTRAINT [PK_Bloggers] PRIMARY KEY CLUSTERED ([PK])
)

CREATE TABLE [Profiles] (
	  [PK] int NOT NULL IDENTITY (1,1)
	, [BloggerID] int NOT NULL REFERENCES [Bloggers]([PK])
	, [Updated] datetime NOT NULL DEFAULT GETDATE()
	, CONSTRAINT [PK_Profiles] PRIMARY KEY CLUSTERED ([PK])
)

CREATE TABLE [Blogs] (
	  [PK] int NOT NULL IDENTITY (1,1)
	, [BloggerID] int NOT NULL REFERENCES [Bloggers]([PK])
	, [BlogTitle] varchar(64) NOT NULL
	, [BlogDescription] varchar(256) NOT NULL
	, [Created] datetime NOT NULL DEFAULT GETDATE()
	, [Updated] datetime NOT NULL DEFAULT GETDATE()
	, [AllowComments] bit NOT NULL DEFAULT 0
	, [Deleted] datetime NULL
	, CONSTRAINT [PK_Blogs] PRIMARY KEY CLUSTERED ([PK])
)

CREATE TABLE [BlogPosts] (
	  [PK] int NOT NULL IDENTITY (1,1)
	, [BlogID] int NOT NULL REFERENCES [Blogs]([PK])
	, [PostTitle] varchar(128) NOT NULL
	, [PostContent] varchar(MAX) NOT NULL
	, [Created] datetime NOT NULL DEFAULT GETDATE()
	, [Updated] datetime NOT NULL DEFAULT GETDATE()
	, [Tags] varchar(max) NULL
	, [Published] datetime NULL
	, [Deleted] datetime NULL
	, CONSTRAINT [PK_BlogPosts] PRIMARY KEY CLUSTERED ([PK])
)

CREATE TABLE [BlogPostComments] (
	  [PK] int NOT NULL IDENTITY (1,1)
	, [BlogPostID] int NOT NULL REFERENCES [BlogPosts]([PK])
	, [InReplyTo] int NULL REFERENCES [BlogPostComments]([PK])
	, [BloggerID] int NOT NULL REFERENCES [Bloggers]([PK])
	, [Comment] varchar(MAX) NOT NULL
	, [Posted] datetime NOT NULL DEFAULT GETDATE()
	, [Deleted] datetime NULL
	, CONSTRAINT [PK_BlogPostComments] PRIMARY KEY CLUSTERED ([PK])
)

CREATE TABLE [dbo].[webpages_Membership] (
	[UserId] [int] NOT NULL,
	[CreateDate] [datetime] NULL,
	[ConfirmationToken] [nvarchar](128) NULL,
	[IsConfirmed] [bit] NULL DEFAULT 0,
	[LastPasswordFailureDate] [datetime] NULL,
	[PasswordFailuresSinceLastSuccess] [int] NOT NULL DEFAULT 0,
	[Password] [nvarchar](128) NOT NULL,
	[PasswordChangedDate] [datetime] NULL,
	[PasswordSalt] [nvarchar](128) NOT NULL,
	[PasswordVerificationToken] [nvarchar](128) NULL,
	[PasswordVerificationTokenExpirationDate] [datetime] NULL,
	PRIMARY KEY CLUSTERED ([UserId] ASC)
		WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[webpages_OAuthMembership] (
	[Provider] [nvarchar](30) NOT NULL,
	[ProviderUserId] [nvarchar](100) NOT NULL,
	[UserId] [int] NOT NULL,
	PRIMARY KEY CLUSTERED ([Provider] ASC, [ProviderUserId] ASC)
		WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[webpages_Roles] (
	[RoleId] [int] IDENTITY(1,1) NOT NULL,
	[RoleName] [nvarchar](256) NOT NULL,
	PRIMARY KEY CLUSTERED ([RoleId] ASC)
		WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
	UNIQUE NONCLUSTERED ([RoleName] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[webpages_UsersInRoles] (
	[UserId] [int] NOT NULL REFERENCES [Users] ([UserId]),
	[RoleId] [int] NOT NULL REFERENCES [webpages_Roles] ([RoleId]),
	PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC)
		WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

